<?PHP include_once('header.inc'); ?>
<h2>EEBS Setup</h2>
<P>
	this database setup will fail if the info in 'db_password.inc' is wrong.
	it expects a premade database, the one we have tested with had utf8_unicode_ci collation.
	<BR><BR>
	if you've clicked this link several times, don't worry, it checks whether
	the tables are already made before creating them.
	<BR><BR>
</P>

<?php
require_once 'db.php';

// create user tables
	$eebsdb->query("CREATE TABLE IF NOT EXISTS `users`(
		`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		`username` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
		`email` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
		`group_id` bigint(20) unsigned NOT NULL,
		`salt` varchar(9) COLLATE utf8_unicode_ci NOT NULL,
		`passhash` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
		`imgurl` varchar(80) COLLATE utf8_unicode_ci NOT NULL,

		PRIMARY KEY (`id`),
		UNIQUE KEY `username` (`username`,`email`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;")
		or die("<P>could not create table users</P>");

/* these were in the above table(users), reenter them if using override permissions
		`perm_override_remove` bigint(20) unsigned NOT NULL,
		`perm_override_add` bigint(20) unsigned NOT NULL,
		`reg_date` datetime NOT NULL,
		`last_login_date` datetime NOT NULL,
		`reg_ip` int(10) unsigned NOT NULL,
		`last_login_ip` int(10) unsigned NOT NULL,
		`must_validate` tinyint(1) NOT NULL,
*/

/*	$eebsdb->query("CREATE TABLE IF NOT EXISTS `autologin`(
		`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		`user_id` bigint(20) unsigned NOT NULL,
		`public_key` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
		`private_key` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
		`created_date` int(10) unsigned NOT NULL,
		`last_used_date` int(10) unsigned NOT NULL,
		`last_used_ip` int(10) unsigned NOT NULL,
		PRIMARY KEY (`id`),
		KEY `user_id` (`user_id`,`public_key`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;")
		or die(mysql_error());
*/

	$eebsdb->query("CREATE TABLE IF NOT EXISTS `groups`(
		`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		`group_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,

		PRIMARY KEY (`id`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;")
		or die("<P>could not create table groups</P>");

/* these were in the above table(groups), reenter them if using override permissions
		`permissions` bigint(20) unsigned NOT NULL,
*/

/* 	$eebsdb->query("CREATE TABLE IF NOT EXISTS `forced_group_ips` (
		`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		`group_id` bigint(20) unsigned NOT NULL,
		`ip_low` int(10) unsigned NOT NULL,
		`ip_high` int(10) unsigned NOT NULL,
		PRIMARY KEY (`id`),
		KEY `ip_low` (`ip_low`,`ip_high`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;")
		or die(mysql_error());

	$eebsdb->query("CREATE TABLE IF NOT EXISTS `banned_emails` (
		`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		`email_regex` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
		`created_date` datetime NOT NULL,
		`notes` text COLLATE utf8_unicode_ci NOT NULL,
		PRIMARY KEY (`id`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;")
		or die(mysql_error());
 */

/**
 * this section asks the DB for everything in the groups table,
 * it then fetches all the info into an asssociative array before
 * it checks whether the three necessary permission groups are
 * already made, if they aren't, it makes(inserts) them.
 * 
 * it also adds one guest user whose password is randomized so no one can use it 
 * for login
**/
	$grps = $eebsdb->query("SELECT * FROM groups")
		or die("<P>DB query unsuccessfull: groups</P>");
	$grpsarr = $grps->fetchAll();
	if (isset($grpsarr['0'])) {
		echo "<BR>group 1 - bloggers is already in table<BR>";
	} else {
		// add permission group 1
		$eebsdb->query("INSERT INTO groups(
			id, group_name)
			VALUES('1', 'bloggers')")
			or die("<P>could not insert group: bloggers</P>");
		echo "<BR>group 1 - bloggers has been successfully inserted<BR>";
	}
 	if (isset($grpsarr['1'])) {
		echo "<BR>group 2 - moderators is already in table<BR>";
	} else {
		// add permission group 1
		$eebsdb->query("INSERT INTO groups(
			id, group_name)
			VALUES('2', 'moderators')")
			or die("<P>could not insert group: moderators</P>");
		echo "<BR>group 2 - moderators has been successfully inserted<BR>";
	}
	if (isset($grpsarr['2'])) {
		echo "<BR>group 3 - guest is already in table<BR>";
	} else {
		// add permission group 1
		$eebsdb->query("INSERT INTO groups(
			id, group_name)
			VALUES('3', 'guest')")
			or die("<P>could not insert group: guest</P>");
		echo "<BR>group 3 - guest has been successfully inserted<BR>";
	}
	
	$usrs = $eebsdb->query("SELECT * FROM `users`")
		or die("<P>DB query unsuccessfull: user guest</P>");
	$usrarr = $usrs->fetchAll();
	if (isset($usrarr['0'])) {
		echo "<BR>user 1 - guest is already in table<BR>";
	} else {
		$sql = "INSERT INTO `users`(username, group_id, passhash) 
			VALUES('guest', '3', '394675dde7ed58030f548807368f58ca')";
		$eebsdb->query($sql)
			or die("<P>could not insert user: guest</P>");
		echo "<BR>user 1 - guest has been successfully inserted<BR>";
	}
/**
 * blog entry table
**/
	$eebsdb->query("CREATE TABLE IF NOT EXISTS `eebs`.`blogentries` (
		`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
		`user_id` BIGINT UNSIGNED NOT NULL,
		`num_comments` BIGINT UNSIGNED NOT NULL,
		`created_date` int(10) unsigned NOT NULL,
		`title` VARCHAR(200) NOT NULL,
		`entry` TEXT NOT NULL,
		`vote_ups` INT(20) UNSIGNED NOT NULL,
		`vote_downs` INT(20) UNSIGNED NOT NULL,
		`times_read` INT(20) UNSIGNED NOT NULL,
		INDEX (`user_id`))
		ENGINE = MyISAM;")
		or die("<P>could not create table blogentries</P>");
	
/**
 * comments table
**/
	$eebsdb->query("CREATE TABLE IF NOT EXISTS `eebs`.`comments` (
		`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		`blog_id` BIGINT UNSIGNED NOT NULL,

		`created_date` int(10) unsigned NOT NULL,
		`name` VARCHAR(200) NOT NULL,
		`entry` TEXT NOT NULL,
		PRIMARY KEY (`id`))
		ENGINE = MyISAM;")
		or die("<P>could not create table comments</P>");
/**
 * could easily make registered users be recognized by username instead of 
 * "name" with this:
 * `user_id` BIGINT UNSIGNED NOT NULL,
 * 
**/
/*
 * REMEMBER TO UPDATE TABLE AND DATA BELOW IF CHANGING THE DATABASES
**/
?>
<p>
	This text is not shown if the tables were not created successfully.
	 in case they were not and this text is shown, check your database with whatever
	 tool you usually use for database management. all of the listed tables should
	 be present:

	<table>
		<tr>
			<th>Table</th>
			<th>fields</th>
		</tr><tr>
			<td>users</td>
			<td>6</td>
		</tr><tr>
			<td>groups</td>
			<td>2</td>
		</tr><tr>
			<td>blogentries</td>
			<td>9</td>
		</tr><tr>
			<td>comments</td>
			<td>5</td>
		</tr>
<!--
		<tr>
			<td>autologin</td>
			<td>7</td>
		</tr>		
		<tr>
			<td>forced_groups_ips</td>
			<td>6</td>
		</tr>
		<tr>
			<td>banned_emails</td>
			<td>4</td>
		</tr>
-->
	</table>
	totalling 4 tables.
</p>
<?PHP include_once('footer.inc'); ?>